let express = require('express')
let app = express()
let mysql = require("mysql")
let bodyParser = require('body-parser')
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({
	extended: false
}))
//指定静态资源目录
app.use("/", express.static('public'))
let connection = mysql.createConnection({
	host: "180.97.195.131", //
	port: 3306,
	user: "test2",
	password: "test2",
	database: "test2" //
})
//查询个人信息
app.get("/loadGoods", function(req, res) {
	connection.query(`select * from good_list WHERE img!=''`, null, function(err, data) {
		
		if(err){console.log(err)}
		res.json(data)
	})
})
app.get("/userinfo", function(req, res) {
	connection.query(`select * from user_info WHERE id = ?`, [
		req.query.id,
	], function(err, data) {

		if(err) {
			console.log(err)
		}

		res.json(data[0])

	})
})
//修改个人信息
app.post("/changeuserinfo", function(req, res) {
	connection.query(`UPDATE user_info SET user_birth = ? , 
		user_sex = ? , user_edu = ? , user_job = ? , user_pay =? , 
		user_marriage = ? WHERE id =? `,
	[req.body.birth, req.body.sex, req.body.edu, req.body.job, req.body.pay, req.body.marriage, req.body.id], function(err, data) {
		if(err) {
			console.log(err)
			console.log(req.body)
		} else {
			if(data.affectedRows === 1) {
				res.json({
					msg: "修改成功"
				})
			} else {
				res.json({
					msg: "修改失败"
				})
			}

		}
})
		})
//增添用户地址接口
app.post("/addAddress", function(req, res) {
	connection.query(`insert into user_address values (id,?,?)`, [
		req.body.address,
		req.body.userid
	], function(err, data) {

		res.json(data)

	})
})
//此处为请求用户地址
app.get("/appendAddress", function(req, res) {
	connection.query(`select * from user_address WHERE userid=?`, [req.query.userid], function(err, data) {
		if(err) {
			console.log(err)
		}

		res.json(data)
	})
})
//此处为修改用户地址
app.post("/repairAddress", function(req, res) {
	connection.query(`UPDATE user_address SET address=? WHERE userid=?`, [req.body.address, req.body.userid], function(err, data) {
		if(err) {
			console.log(err)
		}
		/*console.log(data)*/
		res.json(data)
	})
})
//此处为删除订单接口
app.post("/delOrder", function(req, res) {
	connection.query(`DELETE FROM order_user WHERE userid = ?`, [req.body.userId], function(err, data) {
		if(err){console.log(err)}
		
		res.json(data)
	})
})

//此处为创建订单接口
app.post("/createOrder", function(req, res) {
	connection.query(`INSERT INTO order_user VALUES(id,?,SYSDATE(),?,?,'等待付款',?,?)`, [req.body.count, req.body.orderNumber, req.body.totalPrice, req.body.userid, req.body.goodsid], function(err, data) {
		if(err) {
			console.log(err)
		}

		res.json(data)
	})
})
//获取订单接口
app.get('/getOrder', function(req, res) {
	connection.query(`SELECT order_user.*, user_info.user_tel, user_info.user_realname, good_list.img, good_list.title FROM order_user INNER JOIN 
user_info on order_user.userid=user_info.id INNER JOIN good_list ON order_user.goodsid=good_list.id WHERE user_info.id=?`, [req.query.userId], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data[0])

		}
	})
})
//订单图片获取
app.get('/getOrderimg', function(req, res) {
	connection.query(`SELECT order_user.*, user_info.user_tel, user_info.user_realname, good_list.img, good_list.title, good_list.unitPrice FROM order_user INNER JOIN
user_info on order_user.userid=user_info.id INNER JOIN good_list ON order_user.goodsid=good_list.id WHERE user_info.id=?`, [req.query.userId], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data)

		}
	})
})

//查询库存
app.get("/checkStock", function(req, res) {
	connection.query(`SELECT good_list.stock FROM good_list WHERE id=?`, [req.query.id], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data[0])

		}
	})
})




//此为创建购物车后台接口
app.post("/addshopcarlist", function(req, res) {
	connection.query(`INSERT INTO shopcarlist VALUES(id,?,?,?)`, [req.body.count, req.body.id, req.body.userid], function(err, data) {
		if(err) {
			console.log(err)
		}

		res.json(data)
	})
})

//此为更新后台商品数量接口（用户点击同一商品时）郭新怡
app.post("/updatecount", function(req, res) {
	connection.query(`UPDATE shopcarlist SET count = ?  WHERE goosid=? AND userid = ?`, [req.body.count, req.body.id, req.body.userid], function(err, data) {
		if(err) {
			console.log(err)
		}

		res.json(data)
	})
})
//生成购物车接口郭新怡
app.get("/loadshoplist", function(req, res) {
	connection.query(`SELECT good_list.*,shopcarlist.* FROM good_list INNER JOIN shopcarlist ON good_list.id=shopcarlist.goosid WHERE shopcarlist.userid=?`, [req.query.id], function(err, data) {
		if(err) {
			console.log(err)
		}

		res.json(data)
	})
})
//登陆后请求购物车数据覆盖cookie郭新怡
app.get("/danduloadshoplist", function(req, res) {
	connection.query(`SELECT * FROM shopcarlist where userid=?`, [req.query.id], function(err, data) {
		if(err) {
			console.log(err)
		}

		res.json(data)
	})
})
//删除后台购物车商品接口
app.post("/delshopcarlist", function(req, res) {
	connection.query(`DELETE FROM shopcarlist WHERE goosid=?`, [req.body.id], function(err, data) {
		if(err) {
			console.log(err)
		}

		res.json(data)
	})
})
//清空购物车数据及后台
app.post("/delAllshopcarlist", function(req, res) {
	connection.query(`DELETE FROM shopcarlist WHERE userid=?`, [req.body.id], function(err, data) {
		if(err) {
			console.log(err)
		}

		res.json(data)
	})
})
//列表面包屑导航//返回值1是暴风魔镜，17是暴风VIP，24 是娱乐周边
app.get("/listbreadnav", function(req, res) {
	connection.query(`SELECT g.typeid,g.parentid,l.name FROM good_list g 
INNER JOIN listpage_type l on l.goodid = g.typeid where g.id = ?`, [req.query.id], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data[0])
		}
	})
})
//详情面包屑导航
app.get("/goodsbreadnav", function(req, res) {
	connection.query(`SELECT g.id,gl.title'firsttitle',gl.id'firstid',l.name'secondtitle',l.id'secondid',g.title'thirdtitle' FROM good_list g 
INNER JOIN listpage_type l on l.goodid = g.typeid
INNER JOIN good_list gl on gl.id = g.parentid
 where g.id = ?`, [req.query.id], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data[0])
		}
	})
})

//商品详情页接口
app.get("/goods", function(req, res) {
	connection.query(`select * from good_list where id = ?`, [req.query.id], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data[0])
		}
	})
})
//商品title图接口
app.get("/goods_title_img", function(req, res) {
	connection.query(`select imgadd from goods_img where goodsid = ? and typeid = 1`, [req.query.id], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data)
		}
	})
})
//商品desc图接口
app.get("/goods_desc_img", function(req, res) {
	connection.query(`select imgadd from goods_img where goodsid = ? and typeid = 2`, [req.query.id], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data)
		}
	})
})
//注册接口
//注册直接登录接口
app.get("/registerlogin", function(req, res) {
	connection.query(`select * from user_info where user_tel= ?`, [req.query.tel], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data)
		}
	})
})
//登录接口
app.post("/register", function(req, res) {
	connection.query(`
		INSERT INTO user_info VALUES (id, ?, ?,user_realname,user_addr,user_sex,user_img,user_edu,user_job,user_birth,user_marriage,user_pay)`, [
		req.body.tel, req.body.pwd
	], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			if(data.affectedRows === 1) {
				res.json({
					msg: "注册成功"
				})
			} else {
				res.json({
					msg: "注册失败"
				})
			}
		}
	})
})
//手机号重复注册接口
app.get("/register", function(req, res) {
	connection.query(`select count(*) count from user_info where user_tel=?`, [req.query.tel], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			if(data[0].count === 1) {
				res.json({
					success: false
				})
			} else {
				res.json({
					success: true
				})
			}

		}
	})

})
//登录接口
app.post('/login', function(req, res) {
	connection.query(`select * from user_info where user_tel=? and user_pwd=?`, [req.body.tel, req.body.pwd], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data)

		}
	})
})
//head用户名
app.get('/loginname', function(req, res) {
	connection.query(`select * from user_info where id = ?`, [req.query.userid], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data[0])

		}
	})
})
//修改密码
app.post('/changepawd', function(req, res) {

	connection.query(`UPDATE user_info SET user_pwd = ? WHERE id =? 
		and user_pwd =?`, [req.body.newpwd, req.body.id, req.body.oldpwd], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			if(data.affectedRows === 1) {
				res.json({
					msg: "恭喜，修改成功"
				})
			} else {
				res.json({
					msg: "修改失败，请检查原密码"
				})
			}

		}
	})
})
//增加喜欢商品
app.post('/goodslikeadd', function(req, res) {
	connection.query(`INSERT INTO goods_favourite VALUES (id,?,?)`, [req.body.userid, req.body.goodsid], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			if(data.affectedRows === 1) {
				res.json({
					msg: "添加成功"
				})
			} else {
				res.json({
					msg: "添加失败"
				})
			}
		}
	})
})
//删除喜欢的商品
app.post('/goodslikedel', function(req, res) {
	connection.query(`DELETE FROM goods_favourite WHERE userid = ? and goodsid = ?`, [req.body.userid, req.body.goodsid], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			if(data.affectedRows === 1) {
				res.json({
					msg: "删除成功"
				})
			} else {
				res.json({
					msg: "删除失败"
				})
			}
		}
	})
})
//判断喜欢商品
app.get('/goodslikeif', function(req, res) {
	connection.query(`SELECT count(*) count from goods_favourite 
	where userid = ? and goodsid = ?`, [req.query.userid, req.query.goodsid], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			if(data[0].count === 1) {
				res.json({
					msg: true
				})
			} else {
				res.json({
					msg: false
				})
			}
		}
	})
})

//查询喜欢商品
app.get('/goodslike', function(req, res) {
	connection.query(`SELECT g.id,g.title , g.img , g.unitPrice , g.oldPrice FROM goods_favourite gf 
		INNER JOIN good_list g ON gf.goodsid = g.id where userid=?`, [req.query.userid], function(err, data) {
		if(err) {
			console.log(err)
		} else {
			res.json(data)
		}
	})
})
//董学林接口
app.get('/typelist', function(req, res) {
	connection.query(`select * from listpage_type  WHERE goodid < 4`, [],
		function(err, data) {
			if(err) {
				console.log(err)
			} else {
				res.json(data)

			}
		})

})
//列表页接口
app.get('/listPage', function(req, res) {
	connection.query(`SELECT g.id,g.img,g.title,g.unitPrice,g.oldPrice,g.createtime,g.state,g.typeid,g.stock,g.stateWord,g.volume FROM good_list g  WHERE parentid = 1 AND unitPrice != ""`, [],
		function(err, data) {
			if(err) {
				console.log(err)
			} else {
				res.json(data)

			}
		})

})

app.get('/typelist2', function(req, res) {
	connection.query(`SELECT * FROM listpage_type WHERE goodid > 4`, [],
		function(err, data) {
			if(err) {
				console.log(err)
			} else {
				res.json(data)

			}
		})

})

app.get('/listPage2', function(req, res) {
	connection.query(`SELECT g.id,g.img,g.title,g.unitPrice,g.oldPrice,g.createtime,g.state,g.typeid,g.stock,g.stateWord,g.volume FROM good_list g  WHERE parentid = 24 AND unitPrice != ""`, [],
		function(err, data) {
			if(err) {
				console.log(err)
			} else {
				res.json(data)

			}
		})

})

app.get('/getid', function(req, res) {
	connection.query(`SELECT typeid FROM good_list WHERE id = ?`, [req.query.id],
		function(err, data) {
			if(err) {
				console.log(err)
			} else {
				res.json(data[0])

			}
		})

})

app.post('/circums', function(req, res) {
	connection.query(`SELECT g.id,g.img,g.title,g.unitPrice,g.oldPrice,g.createtime,g.state,g.stock,g.stateWord,g.volume FROM good_list g  WHERE typeid=?`, [req.body.typeid],
		function(err, data) {
			if(err) {
				console.log(err)
			} else {
				res.json(data)

			}
		})

})
//获取优惠券购买页面的三个卡种类
app.get('/viplist', function(req, res) {
	connection.query(`SELECT * FROM VIPbuy`, [],
		function(err, data) {
			if(err) {
				console.log(err)
			} else {
				res.json(data)
			}
		})

})

//获取用户所对应的优惠券
app.get('/vipCoupon', function(req, res) {
	connection.query(`SELECT vc.name,vc.value,vc.case,vc.time FROM VIPcoupon vc INNER JOIN VIPcoupon_list vl ON vc.id = vl.vipid INNER JOIN user_info u ON u.id = vl.userid WHERE vl.userid = ?`, [req.query.userid],
		function(err, data) {
			if(err) {
				console.log(err)
			} else {
				res.json(data)
			}
		})

})

app.listen(8000, function() {
	console.log("正在启动，接口为8000")
})